﻿using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore.Infrastructure;
using System.Data.Common;
using System.Data;
using System.Reflection;
using MySqlConnector;

namespace System.Linq;

public static class QueryableExtensions
{
    /// <summary>
    /// 分页查询
    /// </summary>
    /// <typeparam name="TSource"></typeparam>
    /// <param name="source"></param>
    /// <param name="pageIndex">页码，从1开始</param>
    /// <param name="pageSize">每页记录数</param>
    /// <returns></returns>
    public static IQueryable<TSource> Page<TSource>(this IQueryable<TSource> source, int pageIndex = 1, int pageSize = 10)
    {
        return source.Skip((pageIndex - 1) * pageSize).Take(pageSize);
    }

    public static IEnumerable<T> SqlQuery<T>(this DatabaseFacade facade, string sql, params MySqlParameter[] parameters) where T : class, new()
    {
        DataTable dt = SqlQuery(facade, sql, parameters);
        return dt.ToEnumerable<T>();
    }

    public static IEnumerable<T> ToEnumerable<T>(this DataTable dt) where T : class, new()
    {
        PropertyInfo[] propertyInfos = typeof(T).GetProperties();
        T[] ts = new T[dt.Rows.Count];
        int i = 0;
        foreach (DataRow row in dt.Rows)
        {
            T t = new T();
            foreach (PropertyInfo p in propertyInfos)
            {
                if (dt.Columns.IndexOf(p.Name) != -1 && row[p.Name] != DBNull.Value)
                    p.SetValue(t, row[p.Name], null);
            }
            ts[i] = t;
            i++;
        }
        return ts;
    }

    public static DataTable SqlQuery(this DatabaseFacade facade, string sql, params MySqlParameter[] parameters)
    {
        DbCommand cmd = CreateCommand(facade, sql, out DbConnection conn, parameters);
        DbDataReader reader = cmd.ExecuteReader();
        DataTable dt = new DataTable();
        dt.Load(reader);
        reader.Close();
        conn.Close();
        return dt;
    }

    private static DbCommand CreateCommand(DatabaseFacade facade, string sql, out DbConnection dbConn, params MySqlParameter[] parameters)
    {
        DbConnection conn = facade.GetDbConnection();
        dbConn = conn;
        conn.Open();
        DbCommand cmd = conn.CreateCommand();
        cmd.CommandText = sql;
        CombineParams(ref cmd, parameters);
        return cmd;
    }

    private static void CombineParams(ref DbCommand command, params MySqlParameter[] parameters)
    {
        if (parameters != null)
        {
            foreach (MySqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
        }
    }
}
